import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;


public class Query
{
    private Scanner scanner;
	
    private Connection con;
    private  ConnectDb dCon;
    
    public Query(Connection c, ConnectDb d)
    {
        this.con = c;
        this.dCon = d;
        Scanner scanner = new Scanner(System.in);
        
        queryManufac();

    }

    public ArrayList<String> queryManufac()
    {
        String query = "SELECT MANUF FROM MANUF";
        ArrayList<String> temp = new ArrayList<String>();
        try
        {
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            
            while(rs.next())
            	temp.add(rs.getString(1));
                //System.out.println(rs.getString(1));
         
            //System.out.println("Enter the manufacturer: ");
            //scanner = new Scanner(System.in);
            //String manufac = "'"+scanner.nextLine()+"'";
            //manufac = manufac.toUpperCase();
            //System.out.println("you entered:" + manufac);
            //queryModel(manufac);
        }

        catch (SQLException ex) 
        {
            System.err.println(ex.getMessage());
        }
        return temp;
    } 
    
    private void queryModel(String manuf)
    {
        String query = "SELECT tname FROM manuf where manuf=" + manuf;
        
        try
        {
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            String tableName = "";
            while(rs.next())
                tableName = rs.getString(1);
                        
            String query2 = "SELECT distinct model from " + tableName;
            
            try
            {
                Statement stmnt2 = con.createStatement();
                ResultSet rs2 = stmnt2.executeQuery(query2);
                while(rs2.next())
                    System.out.println(rs2.getString(1));
                
                System.out.println("Enter the model: ");
                scanner = new Scanner(System.in);
                String model = "'"+scanner.nextLine()+"'";
                model = model.toUpperCase();
                queryYear(model, tableName);
            }
            
            catch (SQLException ex) 
            {
            System.err.println(ex.getMessage());
            }
        }
        
        
        catch (SQLException ex)
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryYear(String model, String tName)
    {

        String query = "Select DISTINCT year from " + tName + "where model =" + model;
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
                System.out.println(rs.getString(1));
            
            System.out.println("Enter the year: ");
            scanner = new Scanner(System.in);
            String year = "'"+scanner.next()+"'";
            year = year.toUpperCase();
            queryLitres(tName, model, year);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryLitres(String tName, String model, String year)
    {
        String query = "Select DISTINCT litres from " + tName + "where model =" + model + "AND YEAR="+year;
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
                System.out.println(rs.getString(1));
            
            System.out.println("Enter the litres: ");
            scanner = new Scanner(System.in);
            String litres = "'"+scanner.next()+"'";
            litres = litres.toUpperCase();
            queryEngineType(tName, model, year, litres);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryEngineType(String tName, String model, String year, String litres)
    {
        String query = "Select DISTINCT engine_type from " + tName + "where model =" + model + "AND YEAR="+year + "AND litres="+litres;
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
                System.out.println(rs.getString(1));
            
            System.out.println("Enter the engine type: ");
            scanner = new Scanner(System.in);
            String engineType = "'"+scanner.next()+"'";
            engineType = engineType.toUpperCase();
            queryCubicInches(tName, model, year, litres, engineType);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryCubicInches(String tName, String model, String year, String litres, String engineType)
    {
        String query = "Select DISTINCT cubic_inches from " + tName + "where model =" + model + "AND YEAR="+year + "AND litres="+litres + "AND engine_type="+ engineType;
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
                System.out.println(rs.getString(1));
            
            System.out.println("Enter the cubic inches: ");
            scanner = new Scanner(System.in);
            String cubicInches = "'"+scanner.next()+"'";
            cubicInches = cubicInches.toUpperCase();
            queryDesc(tName, model, year, litres, engineType, cubicInches);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryDesc(String tName, String model, String year, String litres, String engineType, String cubicInches)
    {
        String query = "Select DISTINCT description from " + tName + "where model =" + model + "AND YEAR="+year + "AND litres="+litres + "AND engine_type="+ engineType + " AND cubic_inches="+cubicInches;
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
                System.out.println(rs.getString(1));
            
            System.out.println("Enter the description: ");
            scanner = new Scanner(System.in);
            String description = "'"+scanner.nextLine()+"'";
            description = description.toUpperCase();
            queryRlink(tName, model, year, litres, engineType, cubicInches, description);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryRlink(String tName, String model, String year, String litres, String engineType, String cubicInches, String description)
    {
        String query = "Select rlink from " + tName + " where model =" + model + " AND YEAR="+year + " AND litres="+litres + " AND engine_type="+ engineType + " AND cubic_inches=" + cubicInches + " AND description="+description;
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            String rLink = "";
            while(rs.next())
                rLink = rs.getString(1);
            rLink = rLink.trim().toUpperCase();
            System.out.println(rLink);
            queryPNum(rLink);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    
    public void queryPNum(String rLink)
    {
        String query = "Select ARS1,ARS2,ARS3,ARS4,MOD1,MOD2,MOD3,MOD4,BEH1,BEH2,BEH3,BEH4,DAN1,DAN2,DAN3,DAN4 from radCRX where rlink="+rLink;
        /*
 389 17 3/4X26X2 1/4 (3 ROW) 2 3/4 X 18 7/8 3 3/8 X 18 7/8 1 1/2 - LEFT null SADDLE MOUNT 6 IN 179.85 10 
Select part to order, enter the pnum: 
389
asdaw   update rdimars set AMOUNT=9 WHERE P_NUMBER=389
390 17 3/4X2 3/4X1 1/2 (2 ROW) 2 3/4 X 18 7/8 3 3/8 X 18 7/8 1 1/2 - LEFT null SADDLE MOUNT 6 IN 127.2 10 
Select part to order, enter the pnum: 
         * 
         * 
         */
        String[] pnum = new String[16];
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
            {
                for(int i =1; i < 17; i++)
                {
                    pnum[i-1] = "'"+rs.getString(i)+"'";
                }
            }
            
            //print out pnum to test
            for(int i = 0; i<pnum.length; i++)
            {
                //System.out.println(pnum[i]);
                
                for(int j = i+1; j < pnum.length; j++)
                {
                    if (pnum[i].equals(pnum[j]))
                    {
                        pnum[j] = "'null'";
                    }
                }
            }
            
            for(int i = 0; i <pnum.length; i++)
            {
                if(!pnum[i].equals("'null'"))
                {
                    //rdimars
                    if(i<= 3)
                    {
                        queryParts(pnum[i], "rdimars");
                    }
                    //rdimbeh
                    else if (i<=7)
                    {
                        queryParts(pnum[i], "rdimbeh");
                    }
                    //rdimdan
                    else if (i<=11)
                    {
                        queryParts(pnum[i], "rdimdan");
                    }
                    //rdimmod
                    else
                    {
                        queryParts(pnum[i], "rdimmod");
                    }
                }
            }

        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    public void queryParts(String pnum, String table)
    {
        String query = "SELECT * FROM " + table + " WHERE P_NUMBER="+pnum;
        String amount="";
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            String rLink = "";
            while(rs.next())
            {
                for(int i = 1; i<11; i++)
                {
                    rLink = rs.getString(i);
                    if(i==10){
                        amount=rs.getString(i);
                    }
                    System.out.print(rLink+ " ");
                }
                System.out.println("");

            }
            System.out.println("Select part to order, enter the pnum: ");
            scanner = new Scanner(System.in);
            String upnum = scanner.nextLine();
            orderPart(upnum,table,amount);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
public void orderPart(String pnum, String table, String amt){
    int amount = Integer.parseInt(amt);
    amount--;
    String upamt = Integer.toString(amount);

    String query = "update " + table + " set AMOUNT="+upamt+" WHERE P_NUMBER="+pnum;
        
        try
        {    
            
            
    System.out.println("asdaw   "+query);
            Statement stmnt = con.createStatement();
            /*int update = stmnt.executeUpdate(query);
            if(update>0){
                con.commit();
            }
          */
            
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }


}




}
